import pymysql
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session
from constant.constant import dbInfo

# 基类
Base = declarative_base()
# 创建数据库
class Database():
    def __init__(self, host, port, username, password, dbname):
        self.host = host
        self.port = port
        self.username = username
        self.password = password
        self.dbname = dbname
        self.engine = create_engine(
            f"mysql+pymysql://{self.username}:{self.password}@{self.host}:{self.port}/{self.dbname}?charset=utf8",
            max_overflow=0,  # 超过连接池大小外最多创建的连接
            pool_size=20,  # 连接池大小
            pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
            pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
        )
        self.connection = pymysql.connect(
            host=self.host,   # 主机名
            user=self.username,   # 用户名
            password=self.password,    # 密码
            cursorclass=pymysql.cursors.DictCursor
        )
    def createDb(self, dbName):
        cursor = self.connection.cursor()    # 创建游标对象
        cursor.execute(f"CREATE DATABASE IF NOT EXISTS {dbName} CHARACTER SET utf8 COLLATE utf8_general_ci")      # 执行新建数据库的SQL语句
        # 关闭游标
        cursor.close()
        # 关闭数据库连接
        self.connection.close()

    def session(self):
        """
        基于当前连接引擎使用scoped_session获取线程安全session
        :return:
        """
        # 定义一个 session
        Session = sessionmaker(bind= self.engine)
        # session = Session()
        session = scoped_session(Session)  # 后续使用这个session就是线程安全的
        return session

# 数据库操作session
database = Database(dbInfo["host"], dbInfo["port"], dbInfo["username"], dbInfo["password"], dbInfo["dbname"])
db = database.session()
# 创建数据库
def createDataBase(dbName):
    database.createDb(dbName=dbName)
# 基于实体类对象创建数据表
def createTableByBase(tables):
    Base.metadata.create_all(database.engine, tables = tables)
# 基于实体类对象删除数据表
def dropTableByBase(tables):
    Base.metadata.drop_all(database.engine, tables = tables)
